﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Lion.WeiXin.Core.Data
{
   public class RoleDAO :IRole
    {
       /// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return SqlHelper.GetMaxID("AID", "T_Role"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int AID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from T_Role");
			strSql.Append(" where AID=@AID ");
			SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4)			};
			parameters[0].Value = AID;

			return SqlHelper.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(Models.RoleModel model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into T_Role(");
			strSql.Append("RoleName,Remark)");
			strSql.Append(" values (");
			strSql.Append("@RoleName,@Remark)");
			SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4),
					new SqlParameter("@RoleName", SqlDbType.VarChar,50),
					new SqlParameter("@Remark", SqlDbType.VarChar,1000)};
			parameters[1].Value = model.RoleName;
			parameters[2].Value = model.Remark;

			int rows=SqlHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(Models.RoleModel model)
		{
            //StringBuilder strSql=new StringBuilder();
            //strSql.Append("update T_Role set ");
            //strSql.Append("RoleName=@RoleName,");
            //strSql.Append("Remark=@Remark");
            //strSql.Append(" where AID=@AID ");
            //SqlParameter[] parameters = {
            //        new SqlParameter("@RoleName", SqlDbType.VarChar,50),
            //        new SqlParameter("@Remark", SqlDbType.VarChar,1000),
            //        new SqlParameter("@AID", SqlDbType.Int,4)};
            //parameters[0].Value = model.RoleName;
            //parameters[1].Value = model.Remark;
            //parameters[2].Value = model.AID;

            //int rows=SqlHelper.ExecuteSql(strSql.ToString(),parameters);
            //if (rows > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
            //修改角色信息
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("update T_Role set RoleName='{0}',Remark='{1}' where AID={2}", model.RoleName, model.Remark, model.AID);
            //删除当前角色所有 权限
           
            List<string> sqls = new List<string>();
            sqls.Add(strSql.ToString());
           
            StringBuilder strSql2 = new StringBuilder();
            strSql2.AppendFormat("delete T_Role_CMSMenu where Role_ID={0}", model.AID);
            sqls.Add(strSql2.ToString());
            // 添加新权限
            foreach (Models.CmsMenuModel item in model.Menus)
            {
                string sqlAdd = string.Format("insert into T_Role_CMSMenu( Role_ID,Menu_ID) values ({0},{1}) ", model.AID, item.AID);
                sqls.Add(sqlAdd);
            }
          
           
            try
            {
                SqlHelper.ExecuteSqlTran(sqls);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;               
            }           
          
            
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int AID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from T_Role ");
			strSql.Append(" where AID=@AID ");
			SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4)			};
			parameters[0].Value = AID;

			int rows=SqlHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string AIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from T_Role ");
			strSql.Append(" where AID in ("+AIDlist + ")  ");
			int rows=SqlHelper.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Models.RoleModel GetModel(int AID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 AID,RoleName,Remark from T_Role ");
			strSql.Append(" where AID=@AID ");
			SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4)			};
			parameters[0].Value = AID;

			Models.RoleModel model=new Models.RoleModel();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Models.RoleModel DataRowToModel(DataRow row)
		{
			Models.RoleModel model=new Models.RoleModel();
			if (row != null)
			{
				if(row["AID"]!=null && row["AID"].ToString()!="")
				{
					model.AID=int.Parse(row["AID"].ToString());
				}
				if(row["RoleName"]!=null)
				{
					model.RoleName=row["RoleName"].ToString();
				}
				if(row["Remark"]!=null)
				{
					model.Remark=row["Remark"].ToString();
				}
			}
			return model;
		}

		/// <summary>
		/// 返回数据列表
		/// </summary>
		/// <param name="strWhere">查询所有数据，参数为string.Empty</param>
		/// <returns></returns>
		public List<Models.RoleModel> GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select AID,RoleName,Remark ");
			strSql.Append(" FROM T_Role ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
            DataSet ds = SqlHelper.Query(strSql.ToString());
            return ConvertToList(ds);
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public List<Models.RoleModel> GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" AID,RoleName,Remark ");
			strSql.Append(" FROM T_Role ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
            DataSet ds = SqlHelper.Query(strSql.ToString());
            return ConvertToList(ds);
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM T_Role ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = SqlHelper.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public List<Models.RoleModel> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.AID desc");
			}
			strSql.Append(")AS Row, T.*  from T_Role T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);

            DataSet ds = SqlHelper.Query(strSql.ToString());            
            return ConvertToList(ds);
		}

        protected List<Models.RoleModel> ConvertToList(DataSet ds)
        {            
            List<Models.RoleModel> list = new List<Models.RoleModel>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Models.RoleModel model = ConvertToModel(row);
                list.Add(model);
            }
            return list;
        }

        protected Models.RoleModel ConvertToModel(DataRow row)
        {
            if (row == null) { return null; }
            Models.RoleModel model = new Models.RoleModel();
            model.AID = int.Parse(row["AID"].ToString());
            model.RoleName = row["RoleName"].ToString();
            model.Remark = row["Remark"].ToString();
            return model;
        }

    }
}
